package com.hkts.naturalgas.dictionaries.utils;

import com.hkts.naturalgas.association.entity.HktsRequestAddressAssociation;
import com.hkts.naturalgas.datahandler.dataenum.DateEnum;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassNameDinamicaDataSourceUtils
 * @Description
 * @Author yck
 * @Date2021-10-22 9:56
 * @Version V1.0
 **/
@Component
@Slf4j
public class DinamicaDataSourceUtils {
    public  void deleteData(HktsRequestAddressAssociation hktsRequestAddressAssociation, String dataName,List<String> list)  {
        String URL = "jdbc:mysql://" + hktsRequestAddressAssociation.getUrl() + "?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";
        String USER = hktsRequestAddressAssociation.getUsername();
        String PASSWORD = hktsRequestAddressAssociation.getPassword();
        Connection conn=null;
        Statement st = null;
        //1.加载驱动程序
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得数据库链接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            st = conn.createStatement();
            //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
            int totalRow=0;
            String time="datetime";

            if(dataName.equals("duanshaozhukong1")){
                time="日期1";
            }else if(dataName.equals("duanshaozhukong2")){
                time="日期2";
            }
            List<String> sqlList=new ArrayList<String>();
            for(int i=0;i<list.size();i++){
                StringBuffer buffer=new StringBuffer();
                buffer.delete(0,buffer.length());
                buffer.append("delete from ").append(dataName).append(" where "+time+"<'").append(list.get(i)).append("'");
                if(i!=list.size()-1){
                    buffer.append(" and ").append(" DATE_FORMAT("+time+",'%Y-%m-%d %H:%i:%s') not like '").append(DateEnum.getName(i)).append("'");
                }
                log.info("远程删除数据sql:{}",buffer.toString());
                int i1 = st.executeUpdate(buffer.toString());
                totalRow=totalRow+i1;
            }

            log.info(dataName+"删除:{}行",totalRow);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                //关闭资源
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
